package it.mauriziobasile.dietarapida.database;

import it.mauriziobasile.dietarapida.Alimento;
import it.mauriziobasile.dietarapida.DietaRapidaApp;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;

/**
 * 
 * @author Maurizio Basile
 */
public class TabellaAlimenti {

    private static TabellaAlimenti singleton;

    private Hashtable<String, Alimento> hash;

    public Connection connection;

    private String[] colonneDB;

    private TabellaAlimenti() throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, SecurityException, NoSuchMethodException, RuntimeException, InvocationTargetException {
        hash = new Hashtable<String, Alimento>();
        DBConnect db = new DBConnect();
        connection = db.getConnection();
        Statement stmt = connection.createStatement();
        ResultSet res = stmt.executeQuery("SELECT * FROM Nutrienti WHERE PER_EDI>0 ORDER BY 'ALI_DESC'");
        colonneDB = db.getColumns();
        int cc = colonneDB.length;

        while (res.next()) {
            Hashtable<String, String> tmp = new Hashtable<String, String>();
            for (int i = 0; i < cc; i++) {
                tmp.put(colonneDB[i], res.getString(colonneDB[i]));
            }
            Alimento alim = new Alimento(tmp);
            hash.put(alim.getALI_DESC(), alim);
        }

    }

    public Object[] getNomeAlimenti() {
        return hash.keySet().toArray();
    }

    public Object[] getNomeAlimenti(String pattern) {
        Iterator<String> it = hash.keySet().iterator();
        LinkedList<String> ret = new LinkedList<String>();
        while (it.hasNext()) {
            String tmp = (String) it.next();
            if (tmp.toLowerCase().startsWith(pattern.toLowerCase()))
                ret.add(tmp);
        }
        return ret.toArray();
    }

    public static TabellaAlimenti getInstance() {
        if (singleton == null)
            try {
                singleton = new TabellaAlimenti();
            } catch (SecurityException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (IOException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (InstantiationException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (IllegalAccessException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (ClassNotFoundException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (SQLException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (NoSuchMethodException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (RuntimeException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            } catch (InvocationTargetException e) {
                DietaRapidaApp.logger.severe("Eccezione "+e.getMessage());
            }
        return singleton;
    }

    public Alimento get(String nome) {
        return hash.get(nome);
    }

    public void set(Alimento alimento) {
        try {
            updateAlimento(alimento);
        } catch (SQLException e) {
            DietaRapidaApp.logger.severe("Problemi nell aggiornare l'alimento "+e.getMessage());
        }
    }

    private void updateAlimento(Alimento alimento) throws SQLException {
        if (!hash.contains(alimento.getALI_DESC()) && getById(alimento.getALI_ID())!=null){
            //Ho modificato il nome
            String oldName="";
            for (String  tmpAlim : hash.keySet()) {
                Alimento tmp = hash.get(tmpAlim);
                if (tmp.getALI_ID().equals(alimento.getALI_ID())){
                    oldName = tmpAlim;
                    break;
                }
            }
            hash.remove(oldName);
            hash.put(alimento.getALI_DESC(), alimento);
        }
        if (hash.containsKey(alimento.getALI_DESC())){
            StringBuilder query = new StringBuilder();
            query.append("UPDATE Nutrienti SET ");
            Hashtable<String,String> values = new Hashtable<String,String>();
            for (String col : colonneDB) {
                values.put(col,alimento.get(col));
                query.append(col + "=?");
                query.append(",");
            }
            query.setCharAt(query.lastIndexOf(","), ' ');
            query.append("WHERE ALI_ID=?");
            
            
            PreparedStatement stmt = connection.prepareStatement(query.toString());
            int i=1;
            for (String col : colonneDB) {
                if (col.equals("ALI_DESC") || col.equals("NOME_SCIENT"))
                    stmt.setString(i++, values.get(col));
                else{
                    double d=0.0;
                    String v = values.get(col);
                    try{
                        if (v!=null && !v.equals(""))
                            d=Double.parseDouble(v.replace(",","."));
                    }catch(NumberFormatException e){
                        DietaRapidaApp.logger.warning("Non riesco a fare il parseDouble di "+v+" <- colonna: "+col+" "+e.getMessage());
                    }
                    stmt.setDouble(i++, d);
                }
                //stmt.setString(i+1, values.get(i));
            }
            stmt.setString(i, alimento.getALI_ID());
            stmt.executeUpdate();
        }
    }
    
    public Alimento getById(String id){
        for (Alimento alim : hash.values()) {
            if (alim.getALI_ID().equals(id))
                return alim;
        }
        return null;
    }
    
    public void insertAlimento(Alimento alimento) throws SQLException {
        StringBuilder query = new StringBuilder();
        alimento.setALI_ID(nextId());
        query.append("INSERT INTO Nutrienti");
        Hashtable<String,String> values = new Hashtable<String,String>();
        query.append("(");
        for (String col : colonneDB) {
            values.put(col,alimento.get(col));
            query.append(col + ",");
        }
        query.setCharAt(query.lastIndexOf(","), ' ');
        query.append(") VALUES (");
        for (int i=0;i<values.size();i++){
            query.append("?,");
        }
        query.setCharAt(query.lastIndexOf(","), ' ');
        query.append(")");
        
        PreparedStatement stmt = connection.prepareStatement(query.toString());
        int i=1;
        for (String col : colonneDB) {
            if (col.equals("ALI_DESC") || col.equals("NOME_SCIENT"))
                stmt.setString(i++, values.get(col));
            else{
                double d=0.0;
                String v = values.get(col);
                try{
                    if (v!=null && !v.equals(""))
                        d=Double.parseDouble(v.replace(",","."));
                }catch(NumberFormatException e){
                    DietaRapidaApp.logger.warning("Non riesco a fare il parseDouble di "+v+" <- colonna: "+col+" "+e.getMessage());
                }
                stmt.setDouble(i++, d);
            }
        }
        stmt.execute();
        hash.put(alimento.getALI_DESC(), alimento);
    }
    
    private String nextId() throws SQLException{
        int max =0;
        Statement stmt = connection.createStatement();
        if (stmt.execute("SELECT ALI_ID FROM Nutrienti")){
            ResultSet rs = stmt.getResultSet();
            while (rs.next()){
                int nv = 0;
                String id = rs.getString("ALI_ID");
                try{
                    if (id==null)
                        id ="0";
                    nv = Integer.parseInt(id);
                }
                catch(NumberFormatException e){
                    DietaRapidaApp.logger.warning("Non riesco a fare il parseInt di "+id+" <- colonna: "+"ALI_ID"+" "+e.getMessage());
                }
                if (nv>max)
                    max=nv;
            }
        }
        return String.valueOf(max+1);
    }
}
